********************************************************************************
*   Project: Paper 3 - Diabetes Continuum of Care						   *
*   Task: Automate tables with multinomial models		   				   *
*	Author: Dina Goodman (Harvard University)      						   *
*	Code check:      													   *			
*   Date: 29 April 2021													   *
*	Last updated: 23 July 2021											   *
********************************************************************************
*Set globals for entire anaylsis
global Data "/Users/dinagoodman/Dropbox/Harvard/Dissertation/Paper 3/Data"
global Results "/Users/dinagoodman/Dropbox/Harvard/Dissertation/Paper 3/Results"
global Code "/Users/dinagoodman/Dropbox/Harvard/Dissertation/Paper 3/Code"


******Table 2: RRR using baseline data only
cd "$Data"
use "Paper 3_Final BL Dataset_11.7.21.dta", clear 
recode Gender (1=0 "Male") (2=1 "Female"), gen(female)

cd  "$Results"
capture rm "BLmodels_output.xls"
putexcel set "BLmodel_output.xls", replace


*Column Headers

	putexcel A2=("Fixed Effects")
	putexcel B1= ("Aware vs All")
	putexcel B2=("RRR (95% CI)")
	putexcel E2=("P-value")
	putexcel F1= ("Trt vs All")
	putexcel F2=("RRR (95% CI)")
	putexcel I2=("P-value")
	putexcel J1= ("Control vs All")
	putexcel J2=("RRR (95% CI)")
	putexcel M2=("P-value")
	
local row = 3
putexcel A3=("Female (vs male)")

svy: mlogit score region Gender, rrr base(1)
mat rtable = r(table) 

putexcel B`row'= rtable[1,5], nformat(number_d2)
putexcel C`row'= rtable[5,5], nformat("(0.#0;(-#.#0") right  
putexcel D`row'= rtable[6,5], nformat(",0.#0);, -0.#0)") left
putexcel E`row'= rtable[4,5], nformat(0.##0)
putexcel F`row'= rtable[1,8], nformat(number_d2)
putexcel G`row'= rtable[5,8], nformat("(0.#0;(-#.#0") right  
putexcel H`row'= rtable[6,8], nformat(",0.#0);, -0.#0)") left
putexcel I`row'= rtable[4,8], nformat(0.##0)
putexcel J`row'= rtable[1,11], nformat(number_d2)
putexcel K`row'= rtable[5,11], nformat("(0.#0;(-#.#0") right  
putexcel L`row'= rtable[6,11], nformat(",0.#0);, -0.#0)") left
putexcel M`row'= rtable[4,11], nformat(0.##0)

local ++row
di "row is `row'"

putexcel A4=("Age Category")
local ++row

svy: mlogit score Region i.age3cat, rrr base(1)
mat rtable = r(table) 

putexcel B`row' = ("1.0 (Ref)")
local ++row
putexcel B`row'= rtable[1,8], nformat(number_d2)
putexcel C`row'= rtable[5,8], nformat("(0.#0;(-#.#0") right  
putexcel D`row'= rtable[6,8], nformat(",0.#0);, -0.#0)") left
putexcel E`row'= rtable[4,8], nformat(0.##0)
putexcel F`row'= rtable[1,13], nformat(number_d2)
putexcel G`row'= rtable[5,13], nformat("(0.#0;(-#.#0") right  
putexcel H`row'= rtable[6,13], nformat(",0.#0);, -0.#0)") left
putexcel I`row'= rtable[4,13], nformat(0.##0)
putexcel J`row'= rtable[1,18], nformat(number_d2)
putexcel K`row'= rtable[5,18], nformat("(0.#0;(-#.#0") right  
putexcel L`row'= rtable[6,18], nformat(",0.#0);, -0.#0)") left
putexcel M`row'= rtable[4,18], nformat(0.##0)
	local ++row
putexcel B`row'= rtable[1,9], nformat(number_d2)
putexcel C`row'= rtable[5,9], nformat("(0.#0;(-#.#0") right  
putexcel D`row'= rtable[6,9], nformat(",0.#0);, -0.#0)") left
putexcel E`row'= rtable[4,9], nformat(0.##0)
putexcel F`row'= rtable[1,14], nformat(number_d2)
putexcel G`row'= rtable[5,14], nformat("(0.#0;(-#.#0") right  
putexcel H`row'= rtable[6,14], nformat(",0.#0);, -0.#0)") left
putexcel I`row'= rtable[4,14], nformat(0.##0)
putexcel J`row'= rtable[1,19], nformat(number_d2)
putexcel K`row'= rtable[5,19], nformat("(0.#0;(-#.#0") right  
putexcel L`row'= rtable[6,19], nformat(",0.#0);, -0.#0)") left
putexcel M`row'= rtable[4,19], nformat(0.##0)
	local ++row	
di "row is `row'"

//SES
putexcel A8=("SES")
local ++row

svy: mlogit score Region i.ses, rrr base(1)
mat rtable = r(table) 

putexcel B`row' = ("1.0 (Ref)")
local ++row
putexcel B`row'= rtable[1,8], nformat(number_d2)
putexcel C`row'= rtable[5,8], nformat("(0.#0;(-#.#0") right  
putexcel D`row'= rtable[6,8], nformat(",0.#0);,-0.#0)") left
putexcel E`row'= rtable[4,8], nformat(0.##0)
putexcel F`row'= rtable[1,13], nformat(number_d2)
putexcel G`row'= rtable[5,13], nformat("(0.#0;(-#.#0") right  
putexcel H`row'= rtable[6,13], nformat(",0.#0);,-0.#0)") left
putexcel I`row'= rtable[4,13], nformat(0.##0)
putexcel J`row'= rtable[1,18], nformat(number_d2)
putexcel K`row'= rtable[5,18], nformat("(0.#0;(-#.#0") right  
putexcel L`row'= rtable[6,18], nformat(",0.#0);,-0.#0)") left
putexcel M`row'= rtable[4,18], nformat(0.##0)
	local ++row
putexcel B`row'= rtable[1,9], nformat(number_d2)
putexcel C`row'= rtable[5,9], nformat("(0.#0;(-#.#0") right  
putexcel D`row'= rtable[6,9], nformat(",0.#0);,-0.#0)") left
putexcel E`row'= rtable[4,9], nformat(0.##0)
putexcel F`row'= rtable[1,14], nformat(number_d2)
putexcel G`row'= rtable[5,14], nformat("(0.#0;(-#.#0") right  
putexcel H`row'= rtable[6,14], nformat(",0.#0);,-0.#0)") left
putexcel I`row'= rtable[4,14], nformat(0.##0)
putexcel J`row'= rtable[1,19], nformat(number_d2)
putexcel K`row'= rtable[5,19], nformat("(0.#0;(-#.#0") right  
putexcel L`row'= rtable[6,19], nformat(",0.#0);,-0.#0)") left
putexcel M`row'= rtable[4,19], nformat(0.##0)
	local ++row	
di "row is `row'"

//Urban vs rural
putexcel A12=("Urban (vs rural)")

svy: mlogit score Region urban, rrr base(1)
mat rtable = r(table) 
putexcel B`row'= rtable[1,5], nformat(number_d2)
putexcel C`row'= rtable[5,5], nformat("(0.#0;(-#.#0") right  
putexcel D`row'= rtable[6,5], nformat(",0.#0);,-0.#0)") left
putexcel E`row'= rtable[4,5], nformat(0.##0)
putexcel F`row'= rtable[1,8], nformat(number_d2)
putexcel G`row'= rtable[5,8], nformat("(0.#0;(-#.#0") right  
putexcel H`row'= rtable[6,8], nformat(",0.#0);,-0.#0)") left
putexcel I`row'= rtable[4,8], nformat(0.##0)
putexcel J`row'= rtable[1,11], nformat(number_d2)
putexcel K`row'= rtable[5,11], nformat("(0.#0;(-#.#0") right  
putexcel L`row'= rtable[6,11], nformat(", 0.#0);, -0.#0)") left
putexcel M`row'= rtable[4,11], nformat(0.##0)
	local ++row	
di "row is `row'"

//Overweight
putexcel A13=("Overweight BMI")

svy: mlogit score Region overweight, rrr base(1)
mat rtable = r(table) 

putexcel B`row'= rtable[1,5], nformat(number_d2)
putexcel C`row'= rtable[5,5], nformat("(0.#0;(-#.#0") right  
putexcel D`row'= rtable[6,5], nformat(", 0.#0);, -0.#0)") left
putexcel E`row'= rtable[4,5], nformat(0.##0)
putexcel F`row'= rtable[1,8], nformat(number_d2)
putexcel G`row'= rtable[5,8], nformat("(0.#0;(-#.#0") right  
putexcel H`row'= rtable[6,8], nformat(", 0.#0);, -0.#0)") left
putexcel I`row'= rtable[4,8], nformat(0.##0)
putexcel J`row'= rtable[1,11], nformat(number_d2)
putexcel K`row'= rtable[5,11], nformat("(0.#0;(-#.#0") right  
putexcel L`row'= rtable[6,11], nformat(", 0.#0);, -0.#0)") left
putexcel M`row'= rtable[4,11], nformat(0.##0)
	local ++row	
di "row is `row'"

//htn
putexcel A14=("Hypertension")
svy: mlogit score Region htn, rrr base(1)
mat rtable = r(table) 

putexcel B`row'= rtable[1,5], nformat(number_d2)
putexcel C`row'= rtable[5,5], nformat("(0.#0;(-#.#0") right  
putexcel D`row'= rtable[6,5], nformat(", 0.#0);, -0.#0)") left
putexcel E`row'= rtable[4,5], nformat(0.##0)
putexcel F`row'= rtable[1,8], nformat(number_d2)
putexcel G`row'= rtable[5,8], nformat("(0.#0;(-#.#0") right  
putexcel H`row'= rtable[6,8], nformat(", 0.#0);, -0.#0)") left
putexcel I`row'= rtable[4,8], nformat(0.##0)
putexcel J`row'= rtable[1,11], nformat(number_d2)
putexcel K`row'= rtable[5,11], nformat("(0.#0;(-#.#0") right  
putexcel L`row'= rtable[6,11], nformat(", 0.#0);, -0.#0)") left
putexcel M`row'= rtable[4,11], nformat(0.##0)
	local ++row	
di "row is `row'"

//highLDL
putexcel A15=("High LDL")
svy: mlogit score Region highLDL, rrr base(1)
mat rtable = r(table) 
putexcel B`row'= rtable[1,5], nformat(number_d2)
putexcel C`row'= rtable[5,5], nformat("(0.#0;(-#.#0") right  
putexcel D`row'= rtable[6,5], nformat(", 0.#0);, -0.#0)") left
putexcel E`row'= rtable[4,5], nformat(0.##0)
putexcel F`row'= rtable[1,8], nformat(number_d2)
putexcel G`row'= rtable[5,8], nformat("(0.#0;(-#.#0") right  
putexcel H`row'= rtable[6,8], nformat(", 0.#0);, -0.#0)") left
putexcel I`row'= rtable[4,8], nformat(0.##0)
putexcel J`row'= rtable[1,11], nformat(number_d2)
putexcel K`row'= rtable[5,11], nformat("(0.#0;(-#.#0") right  
putexcel L`row'= rtable[6,11], nformat(", 0.#0);, -0.#0)") left
putexcel M`row'= rtable[4,11], nformat(0.##0)
	local ++row	
di "row is `row'"


********
******Table 3: RRRs using FU/BL data only
*******

cd "$Data"
use "Paper 3_Final FU Dataset_11.7.21.dta", clear 

cd  "$Results"
capture rm "FUmodels_output.xls"
putexcel set "FUmodel_output.xls", replace

	putexcel B1= ("Decrease vs No Change")
	putexcel B2=("RRR (95% CI)")
	putexcel E2=("P-value")
	putexcel F1= ("Increase vs No change")
	putexcel F2=("RRR (95% CI)")
	putexcel I2=("P-value")

	local row = 3
putexcel A3=("Female (vs male)")
	mlogit score_cat region female, rrr base(1)
	mat rtable = r(table) 

putexcel B`row'= rtable[1,2], nformat(number_d2)
putexcel C`row'= rtable[5,2], nformat("(0.#0;(-0.#0") right  
putexcel D`row'= rtable[6,2], nformat(", 0.#0);, -0.#0)") left
putexcel E`row'= rtable[4,2], nformat(0.##0)
putexcel F`row'= rtable[1,8], nformat(number_d2)
putexcel G`row'= rtable[5,8], nformat("(0.#0;(-0.#0") right  
putexcel H`row'= rtable[6,8], nformat(", 0.#0);, -0.#0)") left
putexcel I`row'= rtable[4,8], nformat(0.##0)

local ++row
di "row is `row'"

putexcel A4=("Age Category")
	local ++row
	mlogit score_cat region i.age3cat, rrr base(1)
	mat rtable = r(table) 
putexcel B`row' = ("1.0 (Ref)")
	local ++row
putexcel B`row'= rtable[1,3], nformat(number_d2)
putexcel C`row'= rtable[5,3], nformat("(0.#0;(-0.#0") right  
putexcel D`row'= rtable[6,3], nformat(", 0.#0);, -0.#0)") left
putexcel E`row'= rtable[4,3], nformat(0.##0)
putexcel F`row'= rtable[1,13], nformat(number_d2)
putexcel G`row'= rtable[5,13], nformat("(0.#0;(-0.#0") right  
putexcel H`row'= rtable[6,13], nformat(", 0.#0);, -0.#0)") left
putexcel I`row'= rtable[4,13], nformat(0.##0)
	local ++row
putexcel B`row'= rtable[1,4], nformat(number_d2)
putexcel C`row'= rtable[5,4], nformat("(0.#0;(-#.#0") right  
putexcel D`row'= rtable[6,4], nformat(",0.#0);, -0.#0)") left
putexcel E`row'= rtable[4,4], nformat(0.##0)
putexcel F`row'= rtable[1,14], nformat(number_d2)
putexcel G`row'= rtable[5,14], nformat("(0.#0;(-#.#0") right  
putexcel H`row'= rtable[6,14], nformat(", 0.#0);, -0.#0)") left
putexcel I`row'= rtable[4,14], nformat(0.##0)
	local ++row	
di "row is `row'"

putexcel A8=("SES")
	local ++row
	mlogit score_cat region i.ses, rrr base(1)
	mat rtable = r(table) 
putexcel B`row' = ("1.0 (Ref)")
local ++row
putexcel B`row'= rtable[1,3], nformat(number_d2)
putexcel C`row'= rtable[5,3], nformat("(0.#0;(-0.#0") right  
putexcel D`row'= rtable[6,3], nformat(", 0.#0);, -0.#0)") left
putexcel E`row'= rtable[4,3], nformat(0.##0)
putexcel F`row'= rtable[1,13], nformat(number_d2)
putexcel G`row'= rtable[5,13], nformat("(0.#0;(-0.#0") right  
putexcel H`row'= rtable[6,13], nformat(", 0.#0);, -0.#0)") left
putexcel I`row'= rtable[4,13], nformat(0.##0)
	local ++row
putexcel B`row'= rtable[1,4], nformat(number_d2)
putexcel C`row'= rtable[5,4], nformat("(0.#0;(-#.#0") right  
putexcel D`row'= rtable[6,4], nformat(",0.#0);, -0.#0)") left
putexcel E`row'= rtable[4,4], nformat(0.##0)
putexcel F`row'= rtable[1,14], nformat(number_d2)
putexcel G`row'= rtable[5,14], nformat("(0.#0;(-#.#0") right  
putexcel H`row'= rtable[6,14], nformat(", 0.#0);, -0.#0)") left
putexcel I`row'= rtable[4,14], nformat(0.##0)
	local ++row	
di "row is `row'"

putexcel A12=("Urban")
	mlogit score_cat region urban, rrr base(1)
	mat rtable = r(table) 

putexcel B`row'= rtable[1,2], nformat(number_d2)
putexcel C`row'= rtable[5,2], nformat("(0.#0;(-0.#0") right  
putexcel D`row'= rtable[6,2], nformat(", 0.#0);, -0.#0)") left
putexcel E`row'= rtable[4,2], nformat(0.##0)
putexcel F`row'= rtable[1,8], nformat(number_d2)
putexcel G`row'= rtable[5,8], nformat("(0.#0;(-0.#0") right  
putexcel H`row'= rtable[6,8], nformat(", 0.#0);, -0.#0)") left
putexcel I`row'= rtable[4,8], nformat(0.##0)

local ++row
di "row is `row'"

putexcel A13=("Overweight BMI")
	mlogit score_cat region overweight_bl, rrr base(1)
	mat rtable = r(table) 
putexcel B`row'= rtable[1,2], nformat(number_d2)
putexcel C`row'= rtable[5,2], nformat("(0.#0;(-0.#0") right  
putexcel D`row'= rtable[6,2], nformat(", 0.#0);, -0.#0)") left
putexcel E`row'= rtable[4,2], nformat(0.##0)
putexcel F`row'= rtable[1,8], nformat(number_d2)
putexcel G`row'= rtable[5,8], nformat("(0.#0;(-0.#0") right  
putexcel H`row'= rtable[6,8], nformat(", 0.#0);, -0.#0)") left
putexcel I`row'= rtable[4,8], nformat(0.##0)


local ++row
di "row is `row'"

putexcel A14=("Hypertension")
	mlogit score_cat region htn_bl, rrr base(1)
	mat rtable = r(table) 

putexcel B`row'= rtable[1,2], nformat(number_d2)
putexcel C`row'= rtable[5,2], nformat("(0.#0;(-0.#0") right  
putexcel D`row'= rtable[6,2], nformat(", 0.#0);, -0.#0)") left
putexcel E`row'= rtable[4,2], nformat(0.##0)
putexcel F`row'= rtable[1,8], nformat(number_d2)
putexcel G`row'= rtable[5,8], nformat("(0.#0;(-0.#0") right  
putexcel H`row'= rtable[6,8], nformat(", 0.#0);, -0.#0)") left
putexcel I`row'= rtable[4,8], nformat(0.##0)


local ++row
di "row is `row'"

putexcel A15=("High cholesterol")
	mlogit score_cat region highldl_bl, rrr base(1)
	mat rtable = r(table) 
putexcel B`row'= rtable[1,2], nformat(number_d2)
putexcel C`row'= rtable[5,2], nformat("(0.#0;(-0.#0") right  
putexcel D`row'= rtable[6,2], nformat(", 0.#0);, -0.#0)") left
putexcel E`row'= rtable[4,2], nformat(0.##0)
putexcel F`row'= rtable[1,8], nformat(number_d2)
putexcel G`row'= rtable[5,8], nformat("(0.#0;(-0.#0") right  
putexcel H`row'= rtable[6,8], nformat(", 0.#0);, -0.#0)") left
putexcel I`row'= rtable[4,8], nformat(0.##0)

local ++row
di "row is `row'"

